Export fields in XLS

caesar · Nov 3, 2005

Hi,

I have a table that contains records
name1| name2
--------------
aaa | 1111
aaa | 222
bbb | 5555
bbb | 222
etc....

I need to export all records from the table in xls files as follow:

if name1 = aaa -> export records in aaa.xls
name1 = bbb -> export records in bbb.xls
an so on...

Can anyone help me?
Thank you.

reclusivemonkey · Nov 3, 2005

Do you want to do this manually or do you want to automate it? The easiest way would be simply to create queries for your criteria, and then export the queries in Excel format.

caesar · Nov 3, 2005

I want to do automatically.
I don't know how to make the link between the field data and name of xls in the DoCmd.TransferSpreadsheet ...method.

How can I translate in code "export all records in xls files as follow :
data_from_first_column.xls will contain the records with data_from_first_column in name1" and so on.
I need an example if is possible.

The table may have more that 1000 records.
I need to do this on click of a command button.
Thank you.

caesar · Nov 4, 2005

Can anyone help me?

reclusivemonkey · Nov 4, 2005

caesar said:
Can anyone help me?

Yeah, some of us do go home and go to sleep you know ;-)

Firstly, set up your queries. I will assume you can create a query that takes the first column from a table.

Once you have this set up, then I would use DoCmd.OutputTo;

Code:

DoCmd.OutputTo acOutputQuery, "qryMyQuery", acFormatXLS, "C:\MyLocation\MyFile.xls"

Obviously replace "qryMyQuery" with your query name, and "C:\MyLocation\MyFile.xls" with wherever you want your file and what to call it. You could write that as a function, and just feed it the query and output every time, if you wanted to be clever...

Edit: I am just reading your post again; I want to check something. Do you want to EXPORT data from ACCESS to EXCEL? Or are you wanting to IMPORT data from EXCEL to ACCESS?

caesar · Nov 4, 2005

reclusivemonkey said:
Yeah, some of us do go home and go to sleep you know ;-)

Firstly, set up your queries. I will assume you can create a query that takes the first column from a table.

Once you have this set up, then I would use DoCmd.OutputTo;

Code:

DoCmd.OutputTo acOutputQuery, "qryMyQuery", acFormatXLS, "C:\MyLocation\MyFile.xls"

Obviously replace "qryMyQuery" with your query name, and "C:\MyLocation\MyFile.xls" with wherever you want your file and what to call it. You could write that as a function, and just feed it the query and output every time, if you wanted to be clever...

Edit: I am just reading your post again; I want to check something. Do you want to EXPORT data from ACCESS to EXCEL? Or are you wanting to IMPORT data from EXCEL to ACCESS?

Thank you for the answer, but the things are not quite easy for me.
Yes , I would like to export data in xls files.
Refering my above example if my table has many records I cannot use
DoCmd.OutputTo acOutputQuery, "qryMyQuery1", acFormatXLS, "C:\MyLocation\aaaa.xls"
DoCmd.OutputTo acOutputQuery, "qryMyQuery1", acFormatXLS, "C:\MyLocation\bbbb.xls"
........
etc..
for each field..
I need something that automate the export, and I don't know how to build the path name of the xls file.
Something like make a Loop and put each record in xls file (if the data from first column of the table is aaaa-> aaa.xls , etc..

reclusivemonkey · Nov 4, 2005

caesar said:
Thank you for the answer, but the things are not quite easy for me.
Yes , I would like to export data in xls files.
Refering my above example if my table has many records I cannot use
DoCmd.OutputTo acOutputQuery, "qryMyQuery1", acFormatXLS, "C:\MyLocation\aaaa.xls"
DoCmd.OutputTo acOutputQuery, "qryMyQuery1", acFormatXLS, "C:\MyLocation\bbbb.xls"
........
etc..
for each field..

From what I understood, you wanted to export ALL records, but only one column. Are you saying you want to export an excel spreadsheet for every criteria in the table? In that case yes, you will need to loop through all the criteria. The OutputTo method is still the way you want to go. I take it you have no experiece of VBA?

caesar · Nov 4, 2005

reclusivemonkey said:
From what I understood, you wanted to export ALL records, but only one column. Are you saying you want to export an excel spreadsheet for every criteria in the table? In that case yes, you will need to loop through all the criteria. The OutputTo method is still the way you want to go. I take it you have no experiece of VBA?

Yes, I want to export ALL records from the table, but all columns.

the name of spreadsheet is give by the name of data from the firs column of the table like this:
| column1 .. ..column x
-------------------------------------------
record1: | data11 ....data1x
record2: | data11 ....data1x
record3: | data11 ....data1x
record4: | data21 ....data2x
record5: | data21 ....data2x
record6: | data21 ....data2x

=> a xls file named data11.xls that contains record1, 2, 3
and a xls file named data21.xls that contains record4, 5, 6

reclusivemonkey · Nov 4, 2005

In that case you need to create a query which groups the data in your table to get all the possible criteria. You should then use this query to loop through all the criteria, filtering your query and outputting while you go.

caesar · Nov 4, 2005

reclusivemonkey said:
In that case you need to create a query which groups the data in your table to get all the possible criteria. You should then use this query to loop through all the criteria, filtering your query and outputting while you go.

Thank you for your help.
Unfortunately, it not enough to solve my problem.

Bat17 · Nov 7, 2005

Try this in a new module and make sure that you save it with a name differnt than the functions in it!

Code:

Option Compare Database
Option Explicit

Dim strCriteria As String

Function funGetCriteria()
funGetCriteria = strCriteria
End Function


Sub ExportToXL()
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSql As String
Dim strPath As String

strPath = "C:\MyLocation\"
strSql = "SELECT DISTINCT YourTable.[CriteriaField] FROM YourTable"

Set qdf = CurrentDb.CreateQueryDef("", strSql)
Set rs = qdf.OpenRecordset

Do While Not rs.EOF
    strCriteria = rs![CriteriaField]
    'Debug.Print funGetCriteria
    DoCmd.OutputTo acOutputQuery, "qryMyQuery", acFormatXLS, strPath & strCriteria & ".xls"
    rs.MoveNext
Loop


Set rs = Nothing
Set qdf = Nothing

End Sub

Then create the query that you want to export and for the criteria use "=funGetCriteria()" (no quotes!)

Change the paths/fieldnames/table names in the code to suit your needs

Run ExportToXL

HTH

Peter

caesar · Nov 8, 2005

Thank you Bat17.

That's what I need. It works.

Bat17 · Nov 8, 2005

Hoped it would

Peter

Export fields in XLS

caesar

New member

reclusivemonkey

Registered User.

caesar

New member

caesar

New member

reclusivemonkey

Registered User.

caesar

New member

reclusivemonkey

Registered User.

caesar

New member

reclusivemonkey

Registered User.

caesar

New member

Bat17

Registered User.

caesar

New member

Bat17

Registered User.

Similar threads

Users who are viewing this thread